﻿using System; 
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;

namespace Matrix.MaKaYunShang.DAL
{
	public class McAccountsDAL : BaseDAL<McAccountsDAL>
	{
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(McAccounts model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into McAccounts(");
            strSql.Append("ShopId,MxProjectInfoId,MxWechatInfoUserId,UserName,PasswordHash,LockoutEnabled,LockoutUntil,AccessFailedCount,Created,CreatedBy,Updated,UpdatedBy,Disabled,IsMagAccount,AccountType,MxWechatParamQrCodeId,Mobile,DisplayName,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@ShopId,@MxProjectInfoId,@MxWechatInfoUserId,@UserName,@PasswordHash,@LockoutEnabled,@LockoutUntil,@AccessFailedCount,@Created,@CreatedBy,@Updated,@UpdatedBy,@Disabled,@IsMagAccount,@AccountType,@MxWechatParamQrCodeId,@Mobile,@DisplayName,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@MxProjectInfoId", SqlDbType.Int,4) ,            
                        new SqlParameter("@MxWechatInfoUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@UserName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@PasswordHash", SqlDbType.NVarChar,-1) ,            
                        new SqlParameter("@LockoutEnabled", SqlDbType.Bit,1) ,            
                        new SqlParameter("@LockoutUntil", SqlDbType.DateTime) ,            
                        new SqlParameter("@AccessFailedCount", SqlDbType.Int,4) ,            
                        new SqlParameter("@Created", SqlDbType.DateTime) ,            
                        new SqlParameter("@CreatedBy", SqlDbType.Int,4) ,            
                        new SqlParameter("@Updated", SqlDbType.DateTime) ,            
                        new SqlParameter("@UpdatedBy", SqlDbType.Int,4) ,            
                        new SqlParameter("@Disabled", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsMagAccount", SqlDbType.Bit,1) ,            
                        new SqlParameter("@AccountType", SqlDbType.Int,4) ,            
                        new SqlParameter("@MxWechatParamQrCodeId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Mobile", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@DisplayName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.ShopId;
            parameters[1].Value = model.MxProjectInfoId;
            parameters[2].Value = model.MxWechatInfoUserId;
            parameters[3].Value = model.UserName;
            parameters[4].Value = model.PasswordHash;
            parameters[5].Value = model.LockoutEnabled;
            parameters[6].Value = model.LockoutUntil;
            parameters[7].Value = model.AccessFailedCount;
            parameters[8].Value = model.Created;
            parameters[9].Value = model.CreatedBy;
            parameters[10].Value = model.Updated;
            parameters[11].Value = model.UpdatedBy;
            parameters[12].Value = model.Disabled;
            parameters[13].Value = model.IsMagAccount;
            parameters[14].Value = model.AccountType;
            parameters[15].Value = model.MxWechatParamQrCodeId;
            parameters[16].Value = model.Mobile;
            parameters[17].Value = model.DisplayName;
            parameters[18].Value = model.IsDelete; 
            var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(McAccounts model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update McAccounts set ");

            strSql.Append(" ShopId = @ShopId , ");
            strSql.Append(" MxProjectInfoId = @MxProjectInfoId , ");
            strSql.Append(" MxWechatInfoUserId = @MxWechatInfoUserId , ");
            strSql.Append(" UserName = @UserName , ");
            strSql.Append(" PasswordHash = @PasswordHash , ");
            strSql.Append(" LockoutEnabled = @LockoutEnabled , ");
            strSql.Append(" LockoutUntil = @LockoutUntil , ");
            strSql.Append(" AccessFailedCount = @AccessFailedCount , ");
            strSql.Append(" Created = @Created , ");
            strSql.Append(" CreatedBy = @CreatedBy , ");
            strSql.Append(" Updated = @Updated , ");
            strSql.Append(" UpdatedBy = @UpdatedBy , ");
            strSql.Append(" Disabled = @Disabled , ");
            strSql.Append(" IsMagAccount = @IsMagAccount , ");
            strSql.Append(" AccountType = @AccountType , ");
            strSql.Append(" MxWechatParamQrCodeId = @MxWechatParamQrCodeId , ");
            strSql.Append(" Mobile = @Mobile , ");
            strSql.Append(" DisplayName = @DisplayName , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@MxProjectInfoId", SqlDbType.Int,4) ,            
                        new SqlParameter("@MxWechatInfoUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@UserName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@PasswordHash", SqlDbType.NVarChar,-1) ,            
                        new SqlParameter("@LockoutEnabled", SqlDbType.Bit,1) ,            
                        new SqlParameter("@LockoutUntil", SqlDbType.DateTime) ,            
                        new SqlParameter("@AccessFailedCount", SqlDbType.Int,4) ,            
                        new SqlParameter("@Created", SqlDbType.DateTime) ,            
                        new SqlParameter("@CreatedBy", SqlDbType.Int,4) ,            
                        new SqlParameter("@Updated", SqlDbType.DateTime) ,            
                        new SqlParameter("@UpdatedBy", SqlDbType.Int,4) ,            
                        new SqlParameter("@Disabled", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsMagAccount", SqlDbType.Bit,1) ,            
                        new SqlParameter("@AccountType", SqlDbType.Int,4) ,            
                        new SqlParameter("@MxWechatParamQrCodeId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Mobile", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@DisplayName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.ShopId;
            parameters[2].Value = model.MxProjectInfoId;
            parameters[3].Value = model.MxWechatInfoUserId;
            parameters[4].Value = model.UserName;
            parameters[5].Value = model.PasswordHash;
            parameters[6].Value = model.LockoutEnabled;
            parameters[7].Value = model.LockoutUntil;
            parameters[8].Value = model.AccessFailedCount;
            parameters[9].Value = model.Created;
            parameters[10].Value = model.CreatedBy;
            parameters[11].Value = model.Updated;
            parameters[12].Value = model.UpdatedBy;
            parameters[13].Value = model.Disabled;
            parameters[14].Value = model.IsMagAccount;
            parameters[15].Value = model.AccountType;
            parameters[16].Value = model.MxWechatParamQrCodeId;
            parameters[17].Value = model.Mobile;
            parameters[18].Value = model.DisplayName;
            parameters[19].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update McAccounts set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update McAccounts set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, ShopId, MxProjectInfoId, MxWechatInfoUserId, UserName, PasswordHash, LockoutEnabled, LockoutUntil, AccessFailedCount, Created, CreatedBy, Updated, UpdatedBy, Disabled, IsMagAccount, AccountType, MxWechatParamQrCodeId, Mobile, DisplayName, IsDelete  ");
            strSql.Append("  from McAccounts ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM McAccounts where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion
        #region 扩展方法
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModelByUserName(string userName, int accountId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, ShopId, MxProjectInfoId, MxWechatInfoUserId, UserName, PasswordHash, LockoutEnabled, LockoutUntil, AccessFailedCount, Created, CreatedBy, Updated, UpdatedBy, Disabled, IsMagAccount, AccountType, MxWechatParamQrCodeId, Mobile, DisplayName, IsDelete  ");
            strSql.Append("  from McAccounts ");
            strSql.Append(" where UserName=@UserName and Id!=@AccountId and IsDelete=0 ");
            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                    new SqlParameter("@AccountId", SqlDbType.Int,4)
			};
            parameters[0].Value = userName;
            parameters[1].Value = accountId;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得店铺管理员账号数据列表
        /// </summary>
        public DataSet GetSuperAccountsByShopId(int shopId)
        {
            return GetList(" AccountType = 1 AND ShopId = " + shopId );
        }

	    /// <summary>
	    /// 获取分页数据
	    /// </summary>
        /// <remarks>2016-12-21 万乐园 修改（如为分店，则排除AccountType=1的账号，若为总部，则获取总部旗下所有老板账号）</remarks>
        public List<McAccountDto> GetPagingListForShop(int pageIndex, int pageSize, string shopIds, string searchKeyWord, string colOrder, bool isHeadquarters, ref int totalCount)
        {
            string strWhere = " ShopId in (" + shopIds + ")";

            if (!string.IsNullOrEmpty(searchKeyWord))
            {
                strWhere += " AND (UserName LIKE '%" + searchKeyWord + "%' OR Mobile LIKE '%" + searchKeyWord +
                            "%' OR DisplayName LIKE '%" + searchKeyWord + "%' OR RoleName LIKE '%" + searchKeyWord + "%') ";
            }

	        var table = "(SELECT a.Id AS AccountId, a.ShopId, a.MxProjectInfoId, a.MxWechatInfoUserId, " +
                                 "a.UserName, a.PasswordHash, a.DisplayName, a.Mobile, a.AccountType, ar.RoleId, ss.Name as ShopName, " +
	                             "r.Name AS RoleName, a.IsDelete " +
	                             "FROM McAccounts a " +
	                             "LEFT JOIN McAccountRole ar ON a.Id = ar.AccountId " +
                                 "LEFT JOIN McRoles r ON ar.RoleId = r.Id " +
                                 "LEFT JOIN Shops ss ON a.ShopId = ss.Id " +
                                 "WHERE a.IsDelete = 0 AND ar.IsDelete = 0 AND r.IsDelete = 0 AND a.AccountType" + (isHeadquarters ? "=" : "<>") + "1) n ";

            return GetSimpleListPage<McAccountDto>(pageIndex, pageSize, table, null, null, colOrder, strWhere,
                ref totalCount);
        }

        /// <summary>
        /// 通过参数二维码ID得到一个对象实体
        /// </summary>
        public McAccounts GetModelByParamQrCodeId(int mxWechatParamQrCodeId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, ShopId, MxProjectInfoId, MxWechatInfoUserId,UserName, PasswordHash, LockoutEnabled, LockoutUntil, AccessFailedCount, Created, CreatedBy, Updated, UpdatedBy, Disabled, IsMagAccount, AccountType, MxWechatParamQrCodeId, Mobile, DisplayName, IsDelete  ");
            strSql.Append("  from McAccounts ");
            strSql.Append(" where MxWechatParamQrCodeId=@MxWechatParamQrCodeId  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@MxWechatParamQrCodeId", SqlDbType.Int,4)
			};
            parameters[0].Value = mxWechatParamQrCodeId;

            return DbHelperSQL.ExecuteReader<McAccounts>(strSql.ToString(), parameters);
        }

	    public McAccountDto GetModelWithRoleInfo(int accountId)
	    {
            var sbSql = new StringBuilder();

            sbSql.Append("SELECT ma.Id AS AccountId, ma.ShopId, ma.MxProjectInfoId, ma.MxWechatInfoUserId, ma.UserName,ma.PasswordHash, ma.DisplayName, ma.Mobile, ma.MxWechatParamQrCodeId, ma.AccountType, mr.Id AS RoleId, mr.Name AS RoleName,  ma.IsDelete ");
            sbSql.Append("FROM McAccounts ma ");
            sbSql.Append("LEFT JOIN McAccountRole mar ON ma.Id = mar.AccountId  ");
            sbSql.Append("LEFT JOIN McRoles mr ON mar.RoleId = mr.Id ");
            sbSql.Append("WHERE ma.Id=@AccountId AND ma.IsDelete=0 ");

            SqlParameter[] parameters = {
					new SqlParameter("@AccountId", SqlDbType.Int,4)
			};
            parameters[0].Value = accountId;

            return DbHelperSQL.ExecuteReader<McAccountDto>(sbSql.ToString(), parameters);
	    }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModelByLogin(string username, string passwordHash)
        {
            var sbSql = new StringBuilder();

            sbSql.Append("SELECT ma.Id AS AccountId, ma.ShopId, ma.MxProjectInfoId, ma.MxWechatInfoUserId, ma.UserName, ma.PasswordHash,ma.DisplayName, ma.Mobile, ma.AccountType, ma.MxWechatParamQrCodeId, mr.Id AS RoleId, mr.Name AS RoleName, ma.IsDelete, ss.[Name] AS ShopName,ss.IsHeadquarters,ss.ParentShopId ");
            sbSql.Append("FROM McAccounts ma ");
            sbSql.Append("INNER JOIN Shops ss ON ma.ShopId=ss.Id ");
            sbSql.Append("LEFT JOIN McAccountRole mar ON ma.Id = mar.AccountId  ");
            sbSql.Append("LEFT JOIN McRoles mr ON mar.RoleId = mr.Id ");
            sbSql.Append("WHERE ma.UserName=@UserName AND ma.PasswordHash=@PasswordHash AND ma.IsDelete=0 AND ss.IsDelete=0 and mar.IsDelete=0 ");

            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                    new SqlParameter("@PasswordHash", SqlDbType.NVarChar,-1)
			};
            parameters[0].Value = username;
            parameters[1].Value = passwordHash;
            var ds = DbHelperSQL.Query(sbSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }


        /// <summary>
        /// 根据MxWechatInfoUserId得到一个对象实体
        /// </summary>
        public DataSet GetModelByMxWechatInfoUserId(int mxWechatInfoUserId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, ShopId, MxProjectInfoId, MxWechatInfoUserId,UserName, PasswordHash, LockoutEnabled, LockoutUntil, AccessFailedCount, Created, CreatedBy, Updated, UpdatedBy, Disabled, IsMagAccount, AccountType, MxWechatParamQrCodeId, Mobile, DisplayName, IsDelete  ");
            strSql.Append("  from McAccounts ");
            strSql.Append(" where MxWechatInfoUserId=@MxWechatInfoUserId  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@MxWechatInfoUserId", SqlDbType.Int,4)
			};
            parameters[0].Value = mxWechatInfoUserId;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }
        /// <summary>
        /// 根据账号密码得到一个对象实体
        /// </summary>
  
        public DataSet GetModel(string userName, string pwd)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from McAccounts ");
            strSql.Append(" where [UserName]=@UserName and [PasswordHash]=@PasswordHash and IsDelete=0");
            SqlParameter[] parameters = {
			    new SqlParameter("@UserName", SqlDbType.NVarChar,64),
                new SqlParameter("@PasswordHash", SqlDbType.NVarChar,64)
			};
            parameters[0].Value = userName;
            parameters[1].Value = pwd;
            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 根据店铺ID获取送水工/老板列表
        /// </summary>
        /// <param name="shopId">店铺Id</param>
        /// <param name="role">角色（1为老板，2为店员）</param>
        public List<McAccountDto> GetModelListWithRoleInfoByShopId(int shopId, int role)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT ma.Id AS AccountId, ma.ShopId, ma.MxProjectInfoId, ma.MxWechatInfoUserId, ma.UserName,ma.PasswordHash, ma.DisplayName, ma.MxWechatParamQrCodeId, ma.Mobile, ma.AccountType, mr.Id AS RoleId, mr.Name AS RoleName, ma.IsDelete ");
            sbSql.Append("FROM McAccounts ma ");
            sbSql.Append("LEFT JOIN McAccountRole mar ON ma.Id = mar.AccountId  ");
            sbSql.Append("LEFT JOIN McRoles mr ON mar.RoleId = mr.Id ");
            sbSql.Append("WHERE ma.ShopId=@ShopId AND mr.Id=@Role AND ma.IsDelete=0 ");
            SqlParameter[] parameters = {
				new SqlParameter("@ShopId", SqlDbType.Int,4),
				new SqlParameter("@Role", SqlDbType.Int,4),
			};
            parameters[0].Value = shopId;
            parameters[1].Value = role;
            var lsRet = DbHelperSQL.ExecuteSqlList<McAccountDto>(sbSql.ToString(), parameters);
            return lsRet != null && lsRet.Count > 0 ? lsRet.ToList() : null;
        }

        /// <summary>
        /// 根据微信用户ID获取商家后台用户数据（带权限）
        /// </summary>
        public DataSet GetModelWithRoleInfoByMxWechatInfoUserId(int mxWechatInfoUserId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT ma.Id AS AccountId, ma.ShopId, ma.MxProjectInfoId, ma.MxWechatInfoUserId, ma.UserName,ma.PasswordHash, ma.DisplayName, ma.MxWechatParamQrCodeId, ma.Mobile, ma.AccountType, mr.Id AS RoleId, mr.Name AS RoleName, ma.IsDelete ");
            sbSql.Append("FROM McAccounts ma ");
            sbSql.Append("LEFT JOIN McAccountRole mar ON ma.Id = mar.AccountId  ");
            sbSql.Append("LEFT JOIN McRoles mr ON mar.RoleId = mr.Id ");
            sbSql.Append("WHERE ma.MxWechatInfoUserId=@MxWechatInfoUserId AND ma.IsDelete=0 ");
            SqlParameter[] parameters = {
					new SqlParameter("@MxWechatInfoUserId", SqlDbType.Int,4)
			};
            parameters[0].Value = mxWechatInfoUserId;
            var ds = DbHelperSQL.Query(sbSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 根据店铺ID批量删除一批数据
        /// </summary>
        /// <remarks>2015-07-21 万乐园 创建</remarks>
        public bool DeleteListByShopIds(string shopIds)
        {
            var strSql = new StringBuilder();
            strSql.Append("update McAccounts set IsDelete=1 ");
            strSql.Append(" where ShopId in (" + shopIds + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }
		#endregion
	}
}
